from sqlobject                      import (SQLObject, UnicodeCol, StringCol, 
                                            ForeignKey, MultipleJoin, IntCol,
                                            DateTimeCol, BoolCol, RelatedJoin)
from turbogears.database            import PackageHub
from datetime                       import datetime, timedelta


#
# the TurboGears "package" hub to manage connections / transactions
#

hub = PackageHub("fasttrack")
__connection__ = hub


#
# some constants and mappings to descriptive names
#

CHANCE_LOW          = 1
CHANCE_MEDIUM       = 2
CHANCE_HIGH         = 3
chance_codes        = { CHANCE_LOW:     'Low', 
                        CHANCE_MEDIUM:  'Medium', 
                        CHANCE_HIGH:    'High' }

IMPACT_LOW          = 1
IMPACT_MEDIUM       = 2
IMPACT_HIGH         = 3
impact_codes        = { IMPACT_LOW:     'Low', 
                        IMPACT_MEDIUM:  'Medium', 
                        IMPACT_HIGH:    'High' }

STATUS_NOT_STARTED      = 1
STATUS_ON_SCHEDULE      = 2
STATUS_BEHIND_SCHEDULE  = 3
STATUS_COMPLETE         = 4
status_codes            = { STATUS_NOT_STARTED:     'Not Started', 
                            STATUS_ON_SCHEDULE:     'On Schedule', 
                            STATUS_BEHIND_SCHEDULE: 'Behind Schedule', 
                            STATUS_COMPLETE:        'Complete' }


#
# the actual model objects for Fast Track
#

class Person(SQLObject):
    displayName     = StringCol(alternateID=True)
    userId          = StringCol(alternateID=True)
    password        = StringCol()
    emailAddress    = StringCol()
    
    groups = RelatedJoin("Groups", intermediateTable="tg_user_group",
                         joinColumn="user_id", otherColumn="group_id")
    
    def _get_permissions( self ):
        perms = set()
        for g in self.groups:
            perms = perms | set(g.permissions)
        return perms


class Groups(SQLObject):
    groupId     = StringCol(length=16, alternateID=True)
    displayName = StringCol(length=255)
    created     = DateTimeCol(default=datetime.now)
    
    # collection of all users belonging to this group
    users       = RelatedJoin("Person", 
                              intermediateTable="tg_user_group",
                              joinColumn="group_id", 
                              otherColumn="user_id")
    
    # collection of all permissions for this group
    permissions = RelatedJoin("Permission", 
                              joinColumn="group_id", 
                              intermediateTable="tg_group_permission",
                              otherColumn="permission_id")


class Permission(SQLObject):
    permissionId    = StringCol(length=16, alternateID=True)
    description     = StringCol(length=255)
    groups          = RelatedJoin("Groups", 
                                  intermediateTable="tg_group_permission",
                                  joinColumn="permission_id", 
                                  otherColumn="group_id")


class Project(SQLObject):
    name            = UnicodeCol(alternateID=True)
    description     = UnicodeCol()
    archived        = BoolCol(default=False)
    status          = IntCol(default=STATUS_NOT_STARTED)
    parent_project  = ForeignKey('Project')
    contact         = ForeignKey('Person')
    issues          = MultipleJoin('Issue', orderBy=['-impact', '-creation_date'])
    risks           = MultipleJoin('Risk', orderBy=['-impact', '-creation_date'])
    questions       = MultipleJoin('Question')
    notes           = MultipleJoin('Note', orderBy='-creation_date')
    sub_projects    = MultipleJoin('Project', joinColumn='parent_project_id')
    files           = MultipleJoin('ProjectFile', orderBy='creation_date')
    
    def _get_recent_notes(self):
        now = datetime.now()
        delta = timedelta(15)
        return [note for note in self.notes 
                if (now - note.creation_date <= delta) or
                   (note.last_edit_date is not None and
                    now - note.last_edit_date <= delta)]
    
    def _get_open_risks(self):
        return [risk for risk in self.risks if not risk.closed]
        
    def _get_closed_risks(self):
        return [risk for risk in self.risks if risk.closed]

    def _get_open_issues(self):
        return [issue for issue in self.issues if not issue.closed]

    def _get_closed_issues(self):
        return [issue for issue in self.issues if issue.closed]
    
    def _get_delayed_subprojects(self):
        return [sub_project for sub_project in self.sub_projects 
                if sub_project.status == STATUS_BEHIND_SCHEDULE]

    def _total_questions(self):
        list1 = [question for question in self.questions]
        list2 = [question for sub_project in self.sub_projects for question in sub_project.questions]
        
        return list1 + list2
    
    def _total_answers(self):
        list1 = [answer for question in self.questions for answer in question.answers]
        list2 = [answer for sub_project in self.sub_projects for question in sub_project.questions 
                     for answer in question.answers]
        
        return list1 + list2
    
    def _total_files(self):
        list1 = [file for file in self.files]
        list2 = [file for sub_project in self.sub_projects for file in sub_project.files]
        
        return list1 + list2
        
    def _total_risks(self):
        list1 = [risk for risk in self.risks if not risk.closed]
        list2 = [risk for sub_project in self.sub_projects for risk in sub_project.risks if not risk.closed] 
        
        return list1 + list2

    def _total_issues(self):
        list1 = [issue for issue in self.issues if not issue.closed]
        list2 = [issue for sub_project in self.sub_projects for issue in sub_project.issues if not issue.closed]
        
        return list1 + list2

    def _total_notes(self):
        list1 = [note for note in self.notes]
        list2 = [note for sub_project in self.sub_projects for note in sub_project.notes]
        
        return list1 + list2

    def _new_notes(self, hours=24):
        now = datetime.now()
        delta = timedelta(hours=hours)
        list1 = [note for note in self.notes 
                if (now - note.creation_date <= delta) or
                   (note.last_edit_date is not None and
                    now - note.last_edit_date <= delta)]
                                      
        list2 = [note for sub_project in self.sub_projects for note in sub_project.notes 
                 if (now - note.creation_date <= delta) or
                   (note.last_edit_date is not None and
                    now - note.last_edit_date <= delta)]
        
        return list1 + list2

    def _new_risks(self, hours=24):
        now = datetime.now()
        delta = timedelta(hours=hours)
        list1 = [risk for risk in self.risks if (now - risk.creation_date <= delta and not risk.closed)]
        list2 = [risk for sub_project in self.sub_projects for risk in sub_project.risks 
                     if (now - risk.creation_date <= delta and not risk.closed)]
        
        return list1 + list2

    def _new_issues(self, hours=24):
        now = datetime.now()
        delta = timedelta(hours=hours)
        list1  = [issue for issue in self.issues if (now - issue.creation_date <= delta and not issue.closed)]
        list2 =  [issue for sub_project in self.sub_projects for issue in sub_project.issues 
                   if (now - issue.creation_date <= delta and not issue.closed)]

        return list1 + list2

    def _new_files(self, hours=24):
        now = datetime.now()
        delta = timedelta(hours=hours)
        list1 = [file for file in self.files if (now - file.creation_date <= delta)]
        list2 = [file for sub_project in self.sub_projects for file in sub_project.files 
                     if (now - file.creation_date <= delta)]    
                     
        return list1 + list2
    
    def _new_questions(self, hours=24):
        now = datetime.now()
        delta = timedelta(hours=hours)
        questionList = []
        answerList = []
        list1 = []
        
        
        for question in self.questions:
            if (now - question.creation_date <= delta):
                questionList.append(question)
            for answer in question.answers:
                if (now - answer.creation_date <= delta):
                    answerList.append(answer)
            if len(answerList) > 0 or len(questionList) > 0:
                list1.append(question)
                answerList = []
                questionList = []
        
        answerList = []
        questionList = []
                
        for sub_project in self.sub_projects:
            for question in sub_project.questions:
                if (now - question.creation_date <= delta):
                    questionList.append(question)
                for answer in question.answers:
                    if (now - answer.creation_date <= delta):
                        answerList.append(answer)
                if len(answerList) > 0 or len(questionList) > 0:
                    list1.append(question)            
                    answerList = []
                    questionList = []
            
                    
        return list1    


class Issue(SQLObject):
    issue           = UnicodeCol()
    impact          = IntCol()
    closed          = BoolCol(default=False)
    project         = ForeignKey('Project')
    creator         = ForeignKey('Person')
    creation_date   = DateTimeCol(default=datetime.now)


class Risk(SQLObject):
    chance          = IntCol()
    impact          = IntCol()
    description     = UnicodeCol()
    closed          = BoolCol(default=False)
    creation_date   = DateTimeCol(default=datetime.now)
    project         = ForeignKey('Project')
    creator         = ForeignKey('Person')


class Note(SQLObject):
    note            = UnicodeCol()
    creation_date   = DateTimeCol(default=datetime.now)
    last_edit_date  = DateTimeCol(default=None)
    project         = ForeignKey('Project')
    creator         = ForeignKey('Person')


class Question(SQLObject):
    question        = UnicodeCol()
    creation_date   = DateTimeCol(default=datetime.now)
    project         = ForeignKey('Project')
    creator         = ForeignKey('Person')
    answers         = MultipleJoin('Answer')


class Answer(SQLObject):
    answer          = UnicodeCol()
    creation_date   = DateTimeCol(default=datetime.now)
    question        = ForeignKey('Question')
    creator         = ForeignKey('Person')


class ProjectFile(SQLObject):
    filename        = UnicodeCol()
    systemfilename  = UnicodeCol()
    creation_date   = DateTimeCol(default=datetime.now)
    project         = ForeignKey('Project')
    creator         = ForeignKey('Person')